package com.wxtf.platform.main.jcqkgl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.FormatUtil;
import com.wxtf.platform.tools.XLSUtils;

/**
 * <li>Title: credentials_recordAction.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.credentials_recordAction</li>
 * <li>Description: 独生子女父母光荣证登记</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author wangli
 * @version 1.0.0.0
 * 
 */
public class only_childAction extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(only_childAction.class);

	public only_childAction(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("expData".equals(this.getMethodName())) {
				msg = this.expData(this.getLogicData());
			}else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(user.getUserrelName(), e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}
	
	String[] fields = {"trpocfm_id","tbfi_id","org_id","trpocfm_child_name","trpocfm_child_birthday","trpocfm_child_sex","trpocfm_child_nation",
						"trpocfm_father_name","trpocfm_father_age","trpocfm_father_seniority","trpocfm_mother_name","trpocfm_mother_age",
						"trpocfm_mother_seniority","trpocfm_ocr_time","trpocfm_fee_option","trpocfm_issue_time","trpocfm_memo","reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
						"last_modify_by", "last_modify_time","total"};
	
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		sql = "from trp_only_child_fee_management m LEFT JOIN tb_family_base f on m.tbfi_id=f.tbfi_id LEFT JOIN tb_employee e on f.tbe_id=e.tbe_id where 1=1 ";
		//查询0-18岁的独生子女，超过18岁的不再发放
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String types = jsonObject.getString("types");
			if("init".equals(types)) {// 初始查询
				sql += " and f.org_id in (" + user.getCurSubOrgId() + ")";
			} else if("serch1".equals(types)) {// 条件查询
				String orgIds = jsonObject.getString("orgIds");//机构ID串
				String trpocfm_fee_option = jsonObject.getString("trpocfm_fee_option");//核算标准
				String trpocfm_father_name = jsonObject.getString("trpocfm_father_name");//父亲姓名
				String trpocfm_mother_name = jsonObject.getString("trpocfm_mother_name");//母亲姓名
				String time1 = jsonObject.getString("time1");// 开始时间
				String time2 = jsonObject.getString("time2");// 结束时间
				
				StringBuffer buff = new StringBuffer();
				if(StringUtils.isNotBlank(orgIds)){
					buff.append(" and f.org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
				}
				if(StringUtils.isNotBlank(trpocfm_fee_option)){
					if(!trpocfm_fee_option.equals("3") && trpocfm_fee_option != "")
					{
						buff.append(" and trpocfm_fee_option = '").append(trpocfm_fee_option).append("'");
					}
				}
				
				if(StringUtils.isNotBlank(trpocfm_father_name)){
					buff.append(" and e.tbe_name like '%").append(trpocfm_father_name).append("%'");
				}
				if(StringUtils.isNotBlank(trpocfm_mother_name)){
					buff.append(" and f.tbfi_spouse_name like '%").append(trpocfm_mother_name).append("%'");
				}
				if(StringUtils.isNotBlank(time1)) {
					buff.append(" and trpocfm_ocr_time >= '").append(time1).append("'");
				}
				if(StringUtils.isNotBlank(time2)) {
					buff.append(" and trpocfm_ocr_time <='").append(time2).append(" 23:59:59'");
				}
				
				sql += buff.toString();
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			if("total".equals(sortname))
			{
				sql += " order by " + sortname + " " + sortorder;
			}else
			{
				sql += " order by m." + sortname + " " + sortorder;
			}
		}else{
			sql+=" order by m.last_modify_time desc ";
		}
		
		String countSql = "select count(*) " + sql;
		String querySql = "SELECT trpocfm_id,f.tbfi_id,f.org_id,trpocfm_child_name,trpocfm_child_birthday,trpocfm_child_sex,trpocfm_child_nation,e.tbe_name as trpocfm_father_name,trpocfm_father_age,trpocfm_father_seniority,f.tbfi_spouse_name as trpocfm_mother_name,trpocfm_mother_age,trpocfm_mother_seniority,trpocfm_ocr_time,trpocfm_fee_option,trpocfm_issue_time,trpocfm_memo,(SELECT CAPTION FROM t_department WHERE DEPARTMENT_ID =f.org_id) as reserve1,m.reserve2,m.reserve3,m.reserve4,m.reserve5,m.create_by,m.create_time,m.last_modify_by,m.last_modify_time,'0' as total " + sql;
		Object[] count = {"0"};
//		System.out.println(querySql);
		List<Map<String, String>> list= AccessDataBaseUtil.query4SQLPaggings(fields, countSql, querySql, this.getPage(), this.getPagesize(), count);
		int totals=0;
		String json = "[]";
		JSONArray jsonArraylist = null;
		if(list==null) {
			return "{\"Total\":\"0\",\"TotalMon\":\"" + totals + "\",\"Rows\":[]}";
		} else {
			if(list.size() > 0) {
				for (int i = 0; i < list.size(); i++) {// 行
					int sum=0;
					Map<String, String> map=list.get(i);
//					System.out.println("reserve2:"+map.get("reserve2"));
					if(map.get("reserve2").equals(""))//该独生子女证有效，需要继续计算
					{
						String sqloptions="SELECT trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+map.get("trpocfm_id")+"' order by create_time";
//						System.out.println("mon:"+sqloptions);
						List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
						//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
						for (int j = listopt.size()-1; j >=0 ; j--){// 行
							int option = 0;
							if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
								option = 5;
							} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
								option = 10;
							}
//							System.out.println("option:"+option);
							if(j == listopt.size() - 1) {
								SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
								Date dd = new Date();
//								System.out.println("birthday:"+map.get("trpocfm_child_birthday"));
								//先需要判断独生子女的年龄是否已经满18岁
//								System.out.println("trpocfm_child_birthday:"+(Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4)+"now:"+ft.format(dd));
//								System.out.println("jie"+FormatUtil.remainDateToString((Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4),ft.format(dd)));
								if(FormatUtil.remainDateToString((Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4)))
								{
									int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
									sum += month * option;
//									System.out.println("now");
//									System.out.println("sum:"+sum+"="+month+"*"+option);
									sum = sum<0 ? 0 : sum;
								}
								else
								{
									//直接以出生日期之后的第18年为止
									int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4));
									sum += month * option;
//									System.out.println("18");
//									System.out.println("sum:"+sum+"="+month+"*"+option);
									sum = sum<0 ? 0 : sum;
								}

								if(listopt.size()==2)
								{
									if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
										option = 5;
									} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
										option = 10;
									}
									int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
									sum += mon * option;
									sum = sum<0 ? 0 : sum;
//									System.out.println("sum:"+sum+"="+mon+"*"+option);
								}
							}
							if(listopt.size()!=2)
							{
								if(j >= 1) {
									int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
									sum += month * option;
									//System.out.println("sum:"+sum+"="+month+"*"+option);
								}
							}
						}
						list.get(i).put("total",String.valueOf(sum));
					}
					else//该独生子女证已经无效了，只需要读取曾经发放的金额
					{
						list.get(i).put("total",map.get("reserve2"));
					}
					//System.out.println("total:"+list.get(i).get("total"));
					//totals += sum;
					//System.out.println("sum:"+sum+" totals: "+totals);
				}
				jsonArraylist = JSONArray.fromObject(list);
				json = jsonArraylist.toString();
				
				//统计总金额
				List<Object[]> listtotal = AccessDataBaseUtil.query("select trpocfm_id,m.reserve2,trpocfm_child_birthday " + sql,null);
				if (listtotal.size() > 0) {
					for (int i = 0; i < listtotal.size(); i++) {// 行
						int sum=0;
						Object[] ids=listtotal.get(i);
						if(ids[1].toString().equals(""))//该独生子女证有效，需要继续计算
						{
							String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+ids[0].toString()+"' order by create_time";
							//System.out.println("sqloptions:"+sqloptions);
							List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
							//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
							for (int j = listopt.size()-1; j >=0 ; j--) {// 行
								int option = 0;
								if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
									option = 5;
								} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
									option = 10;
								}
								//System.out.println("option:"+option);
								if(j == listopt.size() - 1) {
									SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
									Date dd = new Date();
									
									//先需要判断独生子女的年龄是否已经满18岁
									//System.out.println("trpocfm_child_birthday:"+(Integer.parseInt(map.get("trpocfm_child_birthday").substring(0, 4))+18)+map.get("trpocfm_child_birthday").substring(4));
									if(FormatUtil.remainDateToString((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4)))
									{
										int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
										sum += month * option;
										sum = sum<0 ? 0 : sum;
									}
									else
									{
										//直接以出生日期之后的第18年为止
										int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(ids[2].toString().substring(0, 4))+18)+ids[2].toString().substring(4));
										sum += month * option;
										sum = sum<0 ? 0 : sum;
									}

									if(listopt.size()==2)
									{
										if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
											option = 5;
										} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
											option = 10;
										}
										int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
										sum += mon * option;
										sum = sum<0 ? 0 : sum;
										//System.out.println("sum:"+sum+"="+mon+"*"+option);
									}
								}
								if(listopt.size()!=2)
								{
									if(j >= 1) {
										int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
										sum += month * option;
										//System.out.println("sum:"+sum+"="+month+"*"+option);
									}
								}
							}
							totals += sum;
							//System.out.println("sum:"+sum+" totals: "+onlytotalMon);
						}else{totals+=Integer.parseInt(ids[1].toString());}
					}
				}

				return "{\"Total\":\"" + count[0] + "\",\"TotalMon\":\"" + totals + "\",\"Rows\":" + json + "}";
			}
			else{return "{\"Total\":\"0\",\"TotalMon\":\"" + totals + "\",\"Rows\":[]}";}
		}
	}	

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		StringBuffer buff = new StringBuffer();
		StringBuffer buff2 = new StringBuffer();
		buff.append("insert into trp_only_child_fee_management(trpocfm_id");
		Object[] obj = new Object[fields.length - 1];
		for (int j = 1; j < fields.length - 1; j++) {
			obj[j] = jsonObject.getString(fields[j]);
			buff.append(",").append(fields[j]);
		}
		buff.append(") values(");
		obj[0] = AccessDataBaseUtil.getPKUUID();

		String curTime = FormatUtil.getCurentDateTime();
		obj[22] = user.getUserId();
		obj[23] = curTime;
		obj[24] = user.getUserId();
		obj[25] = curTime;
		
		for(int i=0; i<obj.length; i++){
			if(i == 9 || i == 15){
				buff2.append(obj[i]).append(",");
			} else {
				buff2.append("'").append(obj[i]).append("',");
			}
		}
		buff2.deleteCharAt(buff2.length() - 1);
		String sql1 = buff.append(buff2.toString()).append(")").toString();
		String[] sqls = { sql1};
		
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"保存成功\"";
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		StringBuffer buff = new StringBuffer();
		buff.append("update trp_only_child_fee_management set ");
		//主键ID不用循环获取，所以要减一
		Object[] obj = new Object[fields.length - 1];
		for(int j = 1; j < fields.length - 1; j++) {
			obj[j - 1] = jsonObject.getString(fields[j]);
			obj[23] = user.getUserId();
			obj[24] = curTime;
			buff.append(fields[j]).append(" = ").append("'").append(obj[j-1]).append("',");

		}
		//同一家庭可能拥有一到两个证，更新的时候以家庭id作为条件
		buff.deleteCharAt(buff.length() - 1).append(" where trpocfm_id = '"+jsonObject.getString("trpocfm_id")+"'");
		
		// 费用表更新的时候，登记情况也要更新
		String sqlman = " update tbe_only_child_record set tbeocr_fee_param = '"+ obj[13]+ "',tbeocr_child_name = '"+obj[2]+"',tbeocr_child_sex = '"+obj[4]+"',tbeocr_child_birthday = '"+obj[3]+"' where tbfi_id ='"+ obj[0]+ "'";

		String manSql = "select create_time,trpocfm_id,trpocfo_id,trpocfm_fee_option from trp_only_child_fee_options where trpocfm_id = ? order by create_time desc";
		String[] field = {"create_time","trpocfm_id","trpocfo_id","trpocfm_fee_option"};
		List<Map<String, String>> lmanage = AccessDataBaseUtil.query(field,manSql,new Object[] { jsonObject.getString("trpocfm_id") });
		String[] sqls;
		//查看记录数据是否还存在
		if(lmanage.size()>0)
		{
			Map<String, String> map = lmanage.get(0);
			//如果标准没有变化，不需要更新操作
			if(!map.get("trpocfm_fee_option").toString().equals(obj[13]))
			{
				//如果是当前月份的话，直接更新[比较年月]
				String time = map.get("create_time").toString().split(" ")[0].substring(0, 7);
				Date time1=new Date();
				SimpleDateFormat myFormatter = new SimpleDateFormat("yyyy-MM-dd");   
				String date= myFormatter.format(time1).substring(0, 7); 
	
				if(time.equals(date))
				{
					String sqlopt = " update trp_only_child_fee_options set trpocfm_fee_option='"+obj[13]+"' where trpocfo_id = '"+map.get("trpocfo_id")+"'";
					sqls = new String[] { buff.toString(),sqlman,sqlopt };
				}
				else
				{
					//如果不是当月的话，需要插入
					String sqlopt = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values" +
							"('" + AccessDataBaseUtil.getPKUUID() + "','" + jsonObject.getString("trpocfm_id")+ "','" + obj[13] + "','" + obj[16] + "','" + obj[17] + "','" + obj[18] + "','" + obj[19] + "','" + obj[20] + "','" + obj[21] + "','" + obj[22] + "','" + obj[23] + "','" + obj[24] + "')";
					sqls = new String[] { buff.toString(),sqlman,sqlopt };
				}
			}else{sqls = new String[] { buff.toString(),sqlman};}
		}
		else
		{
			//记录表
			String sqlfee = "insert into trp_only_child_fee_options(trpocfo_id,trpocfm_id,trpocfm_fee_option,reserve1, reserve2, reserve3, reserve4, reserve5, create_by, create_time,last_modify_by, last_modify_time) values" +
					"('" + AccessDataBaseUtil.getPKUUID() + "','" + jsonObject.getString("trpocfm_id")+ "','" + obj[13] + "','" + obj[16] + "','" + obj[17] + "','" + obj[18] + "','" + obj[19] + "','" + obj[20] + "','" + obj[21] + "','" + obj[22] + "','" + obj[23] + "','" + obj[24] + "')";
			sqls = new String[] { buff.toString(), sqlman,sqlfee};
		}
//		for(int i=0;i<sqls.length;i++)
//		{
//			System.out.println(sqls[i]);
//		}
		
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		String sqldel = "";
		for(int i = 0; i < ids.length; i++) {
			sqldel = sqldel + "delete from trp_only_child_fee_management where trpocfm_id = '"+ids[i]+"'"+",";
			sqldel = sqldel + "delete from trp_only_child_fee_options where trpocfm_id = '"+ids[i]+"'"+",";
		}
		
		String[] sqls =sqldel.split(",");
		AccessDataBaseUtil.executeSQLUpdate(sqls);
		return "\"删除成功\"";
	}

	
	/** 
	 * 描述：expData 人员导出
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 * @CreateOn 
	 * @author wangli
	 */
	public Object expData(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String orgIds = jsonObject.getString("orgIds");//机构ID串
		String trpocfm_father_name = jsonObject.getString("trpocfm_father_name");//父亲姓名
		String trpocfm_mother_name = jsonObject.getString("trpocfm_mother_name");//母亲姓名
		String trpocfm_fee_option = jsonObject.getString("trpocfm_fee_option");//标准
		String time1 = jsonObject.getString("time1");// 开始时间
		String time2 = jsonObject.getString("time2");// 结束时间

		StringBuffer buff = new StringBuffer();
		buff.append("select trpocfm_id,reserve2,reserve1,trpocfm_father_name,trpocfm_mother_name,trpocfm_child_name,trpocfm_child_birthday,");
			if("mysql".equals(user.getDbType())){
				buff.append("if(trpocfm_child_sex='1','女','男') as trpocfm_child_sex,");
			}else if("oracle".equals(user.getDbType())){
				buff.append("decode(trpocfm_child_sex='1','女','男') as trpocfm_child_sex,");
			}
		buff.append("(select tmgd_name from TM_GENERAL_DICTIONARY where tmgd_id = trpocfm_child_nation) as trpocfm_child_nation,")
			.append("trpocfm_ocr_time,case trpocfm_fee_option when '0' then '五元' when '1' then '十元' else ' 无' end,")
			.append("trpocfm_issue_time,' ' as total,trpocfm_memo from trp_only_child_fee_management ");

		
		if(StringUtils.isBlank(orgIds)){
			buff.append(" where org_id in (").append(user.getCurSubOrgId()).append(")");
		} else {
			buff.append(" where org_id in (").append(user.getCurSubOrgIds(orgIds)).append(")");
		}
		if(StringUtils.isNotBlank(trpocfm_fee_option)) {
			if(!"3".equals(trpocfm_fee_option)) {// 全部
				buff.append(" and trpocfm_fee_option = '").append(trpocfm_fee_option).append("'");
			}
		}
		if(StringUtils.isNotBlank(trpocfm_father_name)) {
			buff.append(" and trpocfm_father_name like '%").append(trpocfm_father_name).append("%'");
		}
		if(StringUtils.isNotBlank(trpocfm_mother_name)) {
			buff.append(" and trpocfm_mother_name like '%").append(trpocfm_mother_name).append("%'");
		}
		if(StringUtils.isNotBlank(time1)) {
			buff.append(" and trpocfm_ocr_time >= '").append(time1).append("'");
		}
		if(StringUtils.isNotBlank(time2)) {
			buff.append(" and trpocfm_ocr_time <='").append(time2).append(" 23:59:59'");
		}
		buff.append("order by last_modify_time desc");
		List<Object[]> list = AccessDataBaseUtil.query(buff.toString(), null);
		List<Object[]> list1 = new ArrayList<Object[]>();
		if(list.size() > 0) {
			for(int i = 0; i < list.size(); i++) {
				long sum=0;
				Object[] obj=list.get(i);
//				System.out.println("obj:"+obj[1]);
				if(obj[1].equals(""))
				{
					String sqloptions="Select trpocfm_fee_option,create_time from trp_only_child_fee_options where trpocfm_id = '"+obj[0]+"' order by create_time";
					List<Object[]> listopt = AccessDataBaseUtil.query(sqloptions.toString(), null);
					//最新更新日期与当前时间对比0----1-----2------最新更新-----当前
					for(int j = listopt.size() - 1; j >= 0; j--) {// 行
						int option = 0;
						if(listopt.get(j)[0].equals("五元") || listopt.get(j)[0].equals("0")) {
							option = 5;
						} else if(listopt.get(j)[0].equals("十元") || listopt.get(j)[0].equals("1")) {
							option = 10;
						}
						if(j == listopt.size() - 1) {
							SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd");
							Date dd = new Date();
							//先需要判断独生子女的年龄是否已经满18岁
//							System.out.println("trpocfm_child_birthday:"+(Integer.parseInt(obj[6].toString().substring(0, 4))+18)+obj[6].toString().substring(4));
							if(FormatUtil.remainDateToString((Integer.parseInt(obj[6].toString().substring(0, 4))+18)+obj[6].toString().substring(4),ft.format(dd))<=0||ft.format(dd).equals((Integer.parseInt(obj[6].toString().substring(0, 4))+18)+obj[6].toString().substring(4)))
							{
								int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(), ft.format(dd));
								sum += month * option;
								sum = sum<0 ? 0 : sum;
							}
							else
							{
								//直接以出生日期之后的第18年为止
								list.get(i)[1]="已满18岁";
								int month = FormatUtil.remainDateToString(listopt.get(j)[1].toString(),(Integer.parseInt(obj[6].toString().substring(0, 4))+18)+obj[6].toString().substring(4));
								sum += month * option;
								sum = sum<0 ? 0 : sum;
							}

							if(listopt.size()==2)
							{
								if(listopt.get(0)[0].equals("五元") || listopt.get(0)[0].equals("0")) {
									option = 5;
								} else if(listopt.get(0)[0].equals("十元") || listopt.get(0)[0].equals("1")) {
									option = 10;
								}
								int mon = FormatUtil.remainDateToString(listopt.get(0)[1].toString(), listopt.get(j)[1].toString());
								sum += mon * option;
								sum = sum<0 ? 0 : sum;
								//System.out.println("sum:"+sum+"="+mon+"*"+option);
							}
						}
						if(listopt.size()!=2)
						{
							if(j >= 1) {
								int month = FormatUtil.remainDateToString(listopt.get(j - 1)[1].toString(), listopt.get(j)[1].toString());
								sum += month * option;
								//System.out.println("sum:"+sum+"="+month+"*"+option);
							}
						}
					}
					list.get(i)[12]=String.valueOf(sum);
				}else{
					list.get(i)[12]=String.valueOf(obj[1]);
					list.get(i)[1]="证件已注销";	
				}

				Object[] gg=new Object[list.get(i).length-1];//移除主键trpocfm_id列
				System.arraycopy(list.get(i), 1, gg, 0, list.get(i).length-1);//数组复制
				list1.add(gg);
			}
		}
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		String model = url + "/model/management.xls";
		String file = url + "/file/management.xls";
		String rfile = "jsp/app/xls/file/management.xls";
		File ffrom = new File(model);
		File fto = new File(file);
		Common.copyFile(ffrom, fto);

		if(list.size() > 0){
			XLSUtils.writeXls(list1, fto, "独生子女费一览表", 1);//写xls内容
		} else {
			return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"导出结果集没有记录\"}";
		}
		return "\"" + rfile + "\"";
	}
	
	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
}


